from typing import List
from fastapi import FastAPI, File, Form, UploadFile, Request
from fastapi.staticfiles import StaticFiles
from starlette.responses import FileResponse
from starlette.responses import RedirectResponse
from config import DB
import os
import random
import datetime
import time
import pymysql
import re

app = FastAPI()

app.mount("/static", StaticFiles(directory="static"), name="static")

@app.get("/download/{item_id}")
async def download(item_id):
    try:
        conn = dbConn()
        cursor = conn.cursor()
        sql = "SELECT path, realname, suffix FROM detail WHERE id=%s"
        sqlVal = (item_id)
        cursor.execute(sql, sqlVal)
        fileDetail = cursor.fetchall()
        return FileResponse(os.path.join(os.getcwd(), fileDetail[0][0]), filename=fileDetail[0][1]+fileDetail[0][2])
    except Exception as e:
        return {
            "code": 3000,
            "msg": repr(e)
        }
    finally:
        cursor.close()
        conn.commit()
        conn.close()

@app.get("/data")
async def getData(qstr:str=""):
    try:
        conn = dbConn()
        cursor = conn.cursor()
        if qstr == "":
            sql = "SELECT * FROM detail ORDER BY id DESC LIMIT 30"
            cursor.execute(sql)
            retData = cursor.fetchall()
        else:
            sql = "SELECT * FROM detail WHERE name LIKE %s OR doi LIKE %s ORDER BY id DESC LIMIT 30"
            sqlVal = ('%'+qstr+'%', '%'+qstr+'%')
            cursor.execute(sql, sqlVal)
            retData = cursor.fetchall()
        return {
            "code": 0,
            "msg": "成功",
            "data": retData
        }
    except Exception as e:
        return {
            "code": 3000,
            "msg": repr(e)
        }
    finally:
        cursor.close()
        conn.commit()
        conn.close()

# 重定向
@app.get("/")
async def index(req: Request):
    url = req.url._url + "static/index.html"
    response = RedirectResponse(url=url)
    return response


@app.get("/hello")
async def root():
    return {"message": "Hello World"}

@app.get("/install")
async def install():
    try:
        conn = dbConn(database="")
        cursor = conn.cursor()
        if (data_exists(cursor, "easystore")):
            return {
                "code": 2000,
                "msg": "数据库已存在"
            }
        sql = "CREATE DATABASE IF NOT EXISTS easystore"
        cursor.execute(sql)
        conn.select_db("easystore")
        execSql(cursor)
        return {
            "code": 0,
            "msg": "安装成功"
        }
    except Exception as e:
        return {
            "code": 3000,
            "msg": repr(e)
        }
    finally:
        cursor.close()
        conn.commit()
        conn.close() 

@app.post("/upload")
async def upload( 
    name:str=Form(...), 
    category:str=Form(...),
    source:str=Form(...), 
    dtype:str=Form(...),
    files:List[UploadFile]=File(...),
    content:str=Form(...)):
    try:
        conn = dbConn()
        cursor = conn.cursor()
        for file in files:
            pyPath = os.getcwd()
            nowTime = time.time_ns()
            randNum = random.randint(0, 99999999)
            fileType = getFileType(file)
            year = datetime.datetime.now().year
            month = datetime.datetime.now().month
            day = datetime.datetime.now().day
            cursor.execute("SELECT IF(MAX(id) IS NULL, 0, MAX(id)) AS maxid FROM detail;")
            doinum = int(cursor.fetchall()[0][0])+1
            doi = f"{category}/{source}.{fileType}.{year}-{month}-{day}.{doinum}.User1.{nowTime}-{randNum}"
            filePath = os.path.join('uploads', str(fileType), str(year), str(month))
            filename, suffix = os.path.splitext(file.filename)
            fileName = str(nowTime)+'-'+str(randNum)+suffix
            if not os.path.exists(filePath):
                os.makedirs(filePath)
            fileCont = await file.read()
            with open(os.path.join(pyPath, filePath, fileName), "wb") as f:
                f.write(fileCont)
                insertSql = "INSERT INTO detail(id, doi, name, path, category, source, dtype, content, realname, suffix, type, filename) \
values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                insertVal = (doinum, doi, name, os.path.join(filePath, fileName), category, 
                    source, dtype, content, filename, suffix, fileType, fileName)
                cursor.execute(insertSql, insertVal)
                conn.commit()
        return {
            "code": 0,
            "msg": "保存成功"
        }        
    except Exception as e:
        return {
            "code": 3000,
            "msg": repr(e)
        }
    finally:
        cursor.close()
        conn.commit()
        conn.close()


def getFileType(file):
    fileType = "OTHER"
    if file.content_type in ["image/gif", "image/png", "image/jpeg", "image/bmp", 
        "image/webp", "image/x-icon","image/vnd.microsoft.icon", "image/tiff"]:
        fileType = "P"
    elif file.content_type in ["audio/midi", "audio/mpeg", "audio/webm", "audio/ogg", 
        "audio/wav"]:
        fileType = "A"
    elif file.content_type in ["video/webm", "video/ogg", "video/mp4"]:
        fileType = "V"
    elif file.content_type in ["text/plain", "text/html", "text/css", "text/javascript"]:
        fileType = "T"
    elif file.content_type in ["application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", 
        "application/vnd.ms-excel"]:
        fileType = "XLS"
    elif file.content_type in ["application/msword", 
        "application/vnd.openxmlformats-officedocument.wordprocessingml.document"]:
        fileType = "DOC"
    elif file.content_type in ["application/vnd.openxmlformats-officedocument.presentationml.presentation",
        "application/vnd.ms-powerpoint", "application/vnd.openxmlformats-officedocument.presentationml.slideshow"]:
        fileType = "PPT"
    elif file.content_type in ["application/pdf"]:
        fileType = "PDF"
    return fileType

def execSql(cursor, sqlpath="easystore.sql"):
    with open(os.path.join(os.getcwd(), sqlpath) ,encoding='utf-8',mode='r') as f:
        # 读取整个sql文件，以分号切割。[:-1]删除最后一个元素，也就是空字符串
        sql_list = f.read().split(';')[:-1]
        for x in sql_list:
            # 判断包含空行的
            if '\n' in x:
                # 替换空行为1个空格
                x = x.replace('\n', ' ')
            # 判断多个空格时
            if '    ' in x:
                # 替换为空
                x = x.replace('    ', '')
            # sql语句添加分号结尾
            sql_item = x+';'
            cursor.execute(sql_item)

# 判断数据库是否存在
def data_exists(cursor, database):
    sql = "show databases"
    cursor.execute(sql)
    databases = [cursor.fetchall()]
    databases_list = re.findall('(\'.*?\')', str(databases))
    databases_list = [re.sub("'",'',each) for each in databases_list]
    if database in databases_list:
        return True
    return False

# 连接数据库
def dbConn(host=DB["host"], user=DB["user"], password=DB["password"], database=DB["database"], charset=DB["charset"]):
    if database == "":     
        conn = pymysql.connect(
            host = host,
            user = user,
            password = password,
            charset = charset)
    else:
        conn = pymysql.connect(
            host = host,
            user = user,
            password = password,
            database = database,
            charset = charset)
    return conn